/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Order;
import model.Orderitem;
import model.Product;

/**
 *
 * @author Thai Ha Cong
 */
public class TOrderDAO {

    private Connection con;

    public TOrderDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public List<Order> getOrdersByCustomerID(Integer cid) {
        List<Order> result = new ArrayList<Order>();

        try {
            String sqlst = "SELECT * FROM ordertb"
                    + " WHERE Customer_idCustomer = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, cid);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idOrder = rs.getInt("idOrder");
                Date createTime = new Date(((java.sql.Timestamp) rs.getTimestamp("createTime")).getTime());
                double totalAmount = rs.getDouble("totalAmount");
                int totalQuantity = rs.getInt("totalQuantity");
                String orderStatus = rs.getString("orderStatus");

                Order order = new Order(idOrder, createTime, totalQuantity, totalAmount, orderStatus);
                result.add(order);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Orderitem> getOrderItemListByOrderID(Integer oid) {
        List<Orderitem> result = new ArrayList<Orderitem>();

        try {
            String sqlst = "SELECT * FROM orderitem"
                    + " WHERE Order_idOrder = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, oid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                double orderPrice = rs.getDouble("orderPrice");
                int orderQuantity = rs.getInt("orderQuantity");

                ProductDAO productDAO = new ProductDAO();
                Product product = productDAO.getProductFromProductId(Product_idProduct);

                Orderitem item = new Orderitem(orderQuantity, orderPrice, product);

                result.add(item);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public Order getOrderDetails(Integer oid) {
        Order result = null;

        try {
            String sqlst = "SELECT * FROM ordertb"
                    + " WHERE idOrder = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, oid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idOrder = rs.getInt("idOrder");
                Date createTime = new Date(((java.sql.Timestamp) rs.getTimestamp("createTime")).getTime());
                double totalAmount = rs.getDouble("totalAmount");
                int totalQuantity = rs.getInt("totalQuantity");
                String orderStatus = rs.getString("orderStatus");
                String paypalTransactionId = rs.getString("paypalTransactionId");
                String paypalPaymentStatus = rs.getString("paypalPaymentStatus");
                String paypalReasonCode = rs.getString("paypalReasonCode");
                List<Orderitem> orderitemList = this.getOrderItemListByOrderID(oid);

                result = new Order(idOrder, createTime, totalQuantity, totalAmount, orderStatus, paypalTransactionId, paypalPaymentStatus, paypalReasonCode, orderitemList);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public boolean cancelOrder(Integer id) {
        boolean result = false;

        try {
            String sqlst = "UPDATE ordertb "
                    + "SET orderStatus = ? "
                    + "WHERE idOrder = ? AND orderStatus = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "Cancelled");
            ps.setInt(2, id);
            ps.setString(3, "Processing");

            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(TOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
}
